#!/usr/bin/python
# -*- coding: utf-8 -*-
import sqlite3 as db
database_name = "db.sqlite"

def exec_sql(query):
    try:
        c = db.connect(database=database_name)
        cu = c.cursor()
        cu.execute(query)
        c.commit()
        c.close()
        return True
    except db.DatabaseError, x:
        print "db error: ", x
    except:
        return 'Error'

class dberror(Exception):
  pass
    
class dbquery:
    """ """
    def __init__(self):
        self.c = db.connect(database=database_name)
        self.cu = self.c.cursor()
    def qexec(self, q):
        self.cu.execute(q)
        self.c.commit()
    def cursor(self):
        return self.cu
    def close(self):
        self.c.close()
            

class providers:
    """ manage providers table """
    def __init__(self, id=None):
        if id == None:
            q = dbquery()
            q.qexec("SELECT id FROM providers")
            self.id = int(q.cursor().fetchall()[0][0])
            q.close() 
        else: self.id = id
    def add_provider(self, short_name, full_name="", address="",
                     inn="", kpp="", account="", bank=""):
        """ Add new providers """
        q = dbquery()
        q.qexec("""INSERT INTO providers (short_name, full_name, address,
             inn, kpp, account, bank) VALUES ('%s', '%s', '%s', '%s', '%s', '%s', '%s') 
             """ % (short_name, full_name, address, inn, kpp, account, bank))
        q.close()
    def upd_provider(self, id=None, short_name="", full_name="", address="",
                     inn="", kpp="", account="", bank=""):
        if id == None: id = self.id
        q = []
        if short_name != "": q.append("short_name='%s'" % short_name)
        if full_name != "": q.append("full_name='%s'" % full_name)
        if address != "": q.append("address='%s'" % address)
        if inn != "": q.append("inn='%s'" % inn)
        if kpp != "": q.append("kpp='%s'" % kpp)
        if account != "": q.append("account='%s'" % account)
        if bank != "": q.append("bank='%s'" % bank)
        q =  "UPDATE providers SET " + ", ".join (q) + " WHERE id=%d;" % id
        qq = dbquery()
        qq.qexec(q)
        qq.close()
    def del_provider(self, id=None):
        if id == None: id = self.id
        q = dbquery()
        q.qexec("DELETE FROM providers WHERE id=%d;" % id)
        q.close()
    def get_short_name(self, id=None):
        if id == None: id = self.id
        q = dbquery()
        q.qexec("SELECT short_name FROM providers WHERE id=%d;" % id)
        return q.cursor().fetchall()[0][0]
        q.close()
    def get_full_name(self, id=None):
        if id == None: id = self.id
        q = dbquery()
        q.qexec("SELECT full_name FROM providers WHERE id=%d;" % id)
        return q.cursor().fetchall()[0][0]
        q.close()
    def get_address(self, id=None):
        if id == None: id = self.id
        q = dbquery()
        q.qexec("SELECT address FROM providers WHERE id=%d;" % id)
        return q.cursor().fetchall()[0][0]
        q.close()
    def get_inn(self, id=None):
        if id == None: id = self.id
        q = dbquery()
        q.qexec("SELECT inn FROM providers WHERE id=%d;" % id)
        return q.cursor().fetchall()[0][0]
        q.close()
    def get_kpp(self, id=None):
        if id == None: id = self.id
        q = dbquery()
        q.qexec("SELECT kpp FROM providers WHERE id=%d;" % id)
        return q.cursor().fetchall()[0][0]
        q.close()
    def get_account(self, id=None):
        if id == None: id = self.id
        q = dbquery()
        q.qexec("SELECT account FROM providers WHERE id=%d;" % id)
        return q.cursor().fetchall()[0][0]
        q.close()
    def get_bank(self, id=None):
        if id == None: id = self.id
        q = dbquery()
        q.qexec("SELECT bank FROM providers WHERE id=%d;" % id)
        return q.cursor().fetchall()[0][0]
        q.close()
    def get_provider(self, id=None):
        """ return dict by id"""
        if id == None: id = self.id
        q = dbquery()
        q.qexec("SELECT * FROM providers WHERE id=%d;" % id)
        # 4 fields
        fields_names = []
        for f in q.cursor().description:
            fields_names.append(f[0])
        # get fields
        fields = q.cursor().fetchall()[0]
        q.close()
        # filling table for return 
        table = {fields_names[0]: fields[0],
                 fields_names[1]: fields[1],
                 fields_names[2]: fields[2],
                 fields_names[3]: fields[3],
                 fields_names[4]: fields[4],
                 fields_names[5]: fields[5],
                 fields_names[6]: fields[6],
                 fields_names[7]: fields[7]
                 }
        return table     

    def get_providers(self):
        """ return dict from providers table"""
        q = dbquery()
        q.qexec("SELECT * FROM providers;")
        # get field names
        fields_names = []
        for f in q.cursor().description:
            fields_names.append(f[0])
        # get fields
        fields = q.cursor().fetchall()
        q.close()
        table = []  
        # filling table for return 
        for r in fields:
            record = {fields_names[0]: r[0],
                     fields_names[1]: r[1],
                     fields_names[2]: r[2],
                     fields_names[3]: r[3],
                     fields_names[4]: r[4],
                     fields_names[5]: r[5],
                     fields_names[6]: r[6],
                     fields_names[7]: r[7]
                     }
            table.append(record)
        return table


class products:
    """ manage products table """
    def __init__(self, id=None):
        if id == None:
            q = dbquery()
            q.qexec("SELECT id FROM products;")
            self.id = int(q.cursor().fetchall()[0][0])
            q.close() 
        else: self.id = id
    def add_product(self, product_group, name, unit=""):
        """ Add new product"""
        q = dbquery()
        q.qexec("""INSERT INTO products (product_group, name, unit) 
                VALUES ('%s', '%s', '%s'); 
             """ % (product_group, name, unit))
        q.close()
    def upd_product(self, id=None, product_group=None, unit="", name=""):
        if id == None: id = self.id
        q = []
        if product_group != None: q.append("product_group=%d" % product_group)
        if unit != "": q.append("unit='%s'" % unit)
        if name != "": q.append("name='%s'" % name)
        q =  "UPDATE products SET " + ", ".join (q) + " WHERE id=%d;" % id
        qq = dbquery()
        qq.qexec(q)
        qq.close()
    def del_product(self, id=None):
        if id == None: id = self.id
        q = dbquery()
        q.qexec("DELETE FROM products WHERE id=%d;" % id)
        q.close()
    def get_product_group(self, id=None):
        if id == None: id = self.id
        q = dbquery()
        q.qexec("SELECT product_group FROM products WHERE id=%d;" % id)
        return q.cursor().fetchall()[0][0]
        q.close()
    def get_unit(self, id=None):
        if id == None: id = self.id
        q = dbquery()
        q.qexec("SELECT unit FROM products WHERE id=%d;" % id)
        return q.cursor().fetchall()[0][0]
        q.close()
    def get_name(self, id=None):
        if id == None: id = self.id
        q = dbquery()
        q.qexec("SELECT name FROM products WHERE id=%d;" % id)
        return q.cursor().fetchall()[0][0]
        q.close()
    def get_product(self, id=None):
        """ return dict by id"""
        if id == None: id = self.id
        q = dbquery()
        q.qexec("SELECT * FROM products WHERE id=%d;" % id)
        # 4 fields
        fields_names = []
        for f in q.cursor().description:
            fields_names.append(f[0])
        # get fields
        fields = q.cursor().fetchall()[0]
        q.close()
        # filling table for return 
        table = {fields_names[0]: fields[0],
                 fields_names[1]: fields[1],
                 fields_names[2]: fields[2],
                 fields_names[3]: fields[3]
                 }
        return table     

    def get_products(self):
        """ return dict from products table"""
        q = dbquery()
        q.qexec("SELECT * FROM products;")
        # get field names
        fields_names = []
        for f in q.cursor().description:
            fields_names.append(f[0])
        # get fields
        fields = q.cursor().fetchall()
        q.close()
        table = []  
        # filling table for return 
        for r in fields:
            record = {fields_names[0]: r[0],
                     fields_names[1]: r[1],
                     fields_names[2]: r[2],
                     fields_names[3]: r[3]
                     }
            table.append(record)
        return table

class products_groups:
    """ manage products_groups table """
    def __init__(self, id=None):
        if id == None:
            q = dbquery()
            q.qexec("SELECT id FROM products_groups;")
            self.id = int(q.cursor().fetchall()[0][0])
            q.close() 
        else: self.id = id
    def add_products_groups(self, name):
        """ Add new product group"""
        q = dbquery()
        q.qexec("""INSERT INTO products_groups (name) 
                VALUES ('%s'); 
             """ % name)
        q.close()
    def upd_products_groups(self, name, id=None):
        if id == None: id = self.id
        q =  "UPDATE products_groups SET name='%s' WHERE id=%d;" % (name, id)
        qq = dbquery()
        qq.qexec(q)
    def del_products_groups(self, id=None):
        if id == None: id = self.id
        q = dbquery()
        q.qexec("DELETE FROM products_groups WHERE id=%d;" % id)
        q.close()
    def get_name(self, id=None):
        if id == None: id = self.id
        q = dbquery()
        q.qexec("SELECT name FROM products_groups WHERE id=%d;" % id)
        return q.cursor().fetchall()[0][0]
        q.close()
    def get_products_groups(self):
        """ return dict from products_groups table"""
        q = dbquery()
        q.qexec("SELECT * FROM products_groups;")
        # get field names
        fields_names = []
        for f in q.cursor().description:
            fields_names.append(f[0])
        # get fields
        fields = q.cursor().fetchall()
        q.close()
        table = []  
        # filling table for return 
        for r in fields:
            record = {fields_names[0]: r[0],
                     fields_names[1]: r[1]
                     }
            table.append(record)
        return table        

class income:
    """ manage income table """
    def __init__(self, id=None):
        if id == None:
            q = dbquery()
            q.qexec("SELECT id FROM income;")
            self.id = int(q.cursor().fetchall()[0][0])
            q.close() 
        else: self.id = id    
    def add_income(self, product, provider, date, 
                   nds=0, count=0, price=0):
        """ Add new income product """
        q = dbquery()
        q.qexec("""INSERT INTO income (product, provider, date, nds, count, price) 
                VALUES ('%s'); 
             """ % (product, provider, date, nds, count, price)         
             )
        q.close()
    def upd_income(self, id=None, product=0, provider=0, date=None, nds=0, count=0, price=0):
        if id == None: id = self.id
        q = []
        if product != None: q.append("product=%d" % product)
        if provider != None: q.append("provider=%d" % provider)
        if nds != None: q.append("nds=%d" % nds)
        if count != None: q.append("count=%d" % count)
        if price != "": q.append("price='%s'" % price)
        if date != None: q.append("date='%s'" % date)
        q =  "UPDATE income SET " + ", ".join (q) + " WHERE id=%d;" % id
        qq = dbquery()
        qq.qexec(q)
        qq.close()
    def del_income(self, id=None):
        if id == None: id = self.id
        q = dbquery()
        q.qexec("DELETE FROM income WHERE id=%d;" % id)
        q.close()
    def get_product(self, id=None):
        if id == None: id = self.id
        q = dbquery()
        q.qexec("SELECT product FROM income WHERE id=%d;" % id)
        return q.cursor().fetchall()[0][0]
        q.close()
    def get_provider(self, id=None):
        if id == None: id = self.id
        q = dbquery()
        q.qexec("SELECT provider FROM income WHERE id=%d;" % id)
        return q.cursor().fetchall()[0][0]
        q.close()
    def get_date(self, id=None):
        if id == None: id = self.id
        q = dbquery()
        q.qexec("SELECT date FROM income WHERE id=%d;" % id)
        return q.cursor().fetchall()[0][0]
        q.close()
    def get_nds(self, id=None):
        if id == None: id = self.id
        q = dbquery()
        q.qexec("SELECT nds FROM income WHERE id=%d;" % id)
        return q.cursor().fetchall()[0][0]
        q.close()
    def get_count(self, id=None):
        if id == None: id = self.id
        q = dbquery()
        q.qexec("SELECT count FROM income WHERE id=%d;" % id)
        return q.cursor().fetchall()[0][0]
        q.close()
    def get_price(self, id=None):
        if id == None: id = self.id
        q = dbquery()
        q.qexec("SELECT price FROM income WHERE id=%d;" % id)
        return q.cursor().fetchall()[0][0]
        q.close()
    def get_income(self, id=None):
        """ return dict by id"""
        if id == None: id = self.id
        q = dbquery()
        q.qexec("SELECT * FROM income WHERE id=%d;" % id)
        # 4 fields
        fields_names = []
        for f in q.cursor().description:
            fields_names.append(f[0])
        # get fields
        fields = q.cursor().fetchall()[0]
        q.close()
        # filling table for return 
        table = {fields_names[0]: fields[0],
                 fields_names[1]: fields[1],
                 fields_names[2]: fields[2],
                 fields_names[3]: fields[3],
                 fields_names[4]: fields[4],
                 fields_names[5]: fields[5],
                 fields_names[6]: fields[6]
                 }
        return table 
    def get_incomes(self, date=None):
        """ return dict from income table"""
        q = dbquery()
        q1 = "SELECT * FROM income"
        if date != None: q1 += " WHERE date = '%s';" % date
        else: q1 += ";"
        q.qexec(q1)
        # get field names
        fields_names = []
        for f in q.cursor().description:
            fields_names.append(f[0])
        # get fields
        fields = q.cursor().fetchall()
        q.close()
        table = []  
        # filling table for return 
        for r in fields:
            record = {fields_names[0]: r[0],
                 fields_names[1]: r[1],
                 fields_names[2]: r[2],
                 fields_names[3]: r[3],
                 fields_names[4]: r[4],
                 fields_names[5]: r[5],
                 fields_names[6]: r[6]
                     }
            table.append(record)
        return table
    
        
def get_products_groups_by_id(id):
    name = ''
    try:
        c = db.connect(database=database_name)
        cu = c.cursor()
        cu.execute("SELECT * FROM products_groups WHERE id = %d;" % id)
        for i in cu.fetchall():
            name = i[1]
    except db.DatabaseError, x:
        print "Error: ", x
    return name
def get_products_groups_name(id):
    return get_products_groups_by_id(id)

        
def add_products_groups(name):
    return exec_sql("INSERT INTO products_groups (name) VALUES ('%s');" % name)
def upd_products_groups(id, name):
    return exec_sql("UPDATE products_groups SET name='%s' WHERE id=%d;" % (name, id))
def del_products_groups(id):
    return exec_sql("DELETE FROM products_groups WHERE id=%d;" % id)
def get_product_group(id):
    product_group = ""
    try:
        c = db.connect(database=database_name)
        cu = c.cursor()
        cu.execute("SELECT * FROM products_groups where id =%d;" % id)
        for i in cu.fetchall():
            product_group = i[1]
    except db.DatabaseError, x:
        print "Error: ", x
    return product_group
   
def get_products_groups():
    table = []
    try:
        c = db.connect(database=database_name)
        cu = c.cursor()
        cu.execute("SELECT * FROM products_groups;")
        for i in cu.fetchall():
            table.append({
            'id': i[0],
            'name': i[1]})
    except db.DatabaseError, x:
        print "Error: ", x
    return table
def prodgr_by_product(id):
    name = ""
    try:
        c = db.connect(database=database_name)
        cu = c.cursor()
        cu.execute("""
        SELECT pg.name FROM 
        products_groups pg, products p
        where 
        p.id =%d
        and p.product_group = pg.id;
        """ % id)
        for i in cu.fetchall():
            name = i[0]
    except db.DatabaseError, x:
        print "Error: ", x
    return name 
   
def add_products(product_group, name, unit=""):
    return exec_sql("""INSERT INTO products (product_group, name, unit) 
            VALUES (%d, '%s', '%s');""" % (product_group, name, unit))
def upd_products(id, product_group=0, name="", unit=""):
    q = "UPDATE products SET "
    if product_group != 0: q += " product_group=%d " % product_group
    if name != "": q += ", name='%s' " % name
    if unit != "": q += ", unit='%s' " % unit
    q += "WHERE id=%d;" % id
    return exec_sql(q)
def del_products(id):
    return exec_sql("DELETE FROM products WHERE id=%d;" % id)
def get_products():
    table = []
    try:
        c = db.connect(database=database_name)
        cu = c.cursor()
        cu.execute("SELECT * FROM products;")
        for i in cu.fetchall():
            table.append({
            'id': i[0],
            'product_group': i[1],
            'unit': i[2],
            'name': i[3]})            
    except db.DatabaseError, x:
        print "Error: ", x
    return table

def get_products_by_group(gr):
    table = []
    try:
        c = db.connect(database=database_name)
        cu = c.cursor()
        cu.execute("SELECT * FROM products WHERE product_group = %d;" % gr)
        for i in cu.fetchall():
            table.append({
            'id': i[0],
            'product_group': i[1],
            'unit': i[2],
            'name': i[3]})            
    except db.DatabaseError, x:
        print "Error: ", x
    return table
def get_products_by_id(id):
    table = []
    try:
        c = db.connect(database=database_name)
        cu = c.cursor()
        cu.execute("SELECT * FROM products WHERE id = %d;" % id)
        for i in cu.fetchall():
            table.append({
            'id': i[0],
            'product_group': i[1],
            'unit': i[2],
            'name': i[3]})            
    except db.DatabaseError, x:
        print "Error: ", x
    return table

def get_product_name_by_id(id):
    name = ''
    try:
        c = db.connect(database=database_name)
        cu = c.cursor()
        cu.execute("SELECT * FROM products WHERE id = %d;" % id)
        for i in cu.fetchall():
            name = i[3]
    except db.DatabaseError, x:
        print "Error: ", x
    return name
def get_product_name(id):
    return get_product_name_by_id(id)



def get_product_unit_by_id(id):
    name = ''
    try:
        c = db.connect(database=database_name)
        cu = c.cursor()
        cu.execute("SELECT * FROM products WHERE id = %d;" % id)
        for i in cu.fetchall():
            name = i[2]
    except db.DatabaseError, x:
        print "Error: ", x
    return name

def add_provider(short_name, full_name="", address="",
                 inn="", kpp="", account="", bank=""):
    return exec_sql("""INSERT INTO providers (short_name, full_name, address,
             inn, kpp, account, bank) VALUES ('%s', '%s', '%s', '%s', '%s', '%s', '%s') 
             """ % (short_name, full_name, address, inn, kpp, account, bank))
def upd_provider(id, param):
    return exec_sql("UPDATE providers SET %s WHERE id=%d" % (param, id))
def del_provider(id):
    return exec_sql("DELETE FROM providers WHERE id=%d;" % id)
def get_provider_short_name(id):
    name = ''
    try:
        c = db.connect(database=database_name)
        cu = c.cursor()
        cu.execute("SELECT short_name FROM providers WHERE id = %d;" % id)
        for i in cu.fetchall():
            name = i[0]
    except db.DatabaseError, x:
        print "Error: ", x
    return name

def get_providers():
    table = []
    try:
        c = db.connect(database=database_name)
        cu = c.cursor()
        cu.execute("SELECT * FROM providers;")
        for i in cu.fetchall():
            table.append({
            'id': i[0],
            'short_name': i[1],
            'full_name': i[2],
            'address': i[3],
            'inn': i[4],
            'kpp': i[5],
            'account': i[6],
            'bank': i[7]})            
    except db.DatabaseError, x:
        print "Error: ", x
    return table
    
    
def add_income(product, provider, data, count, price, nds):
    return exec_sql("""INSERT INTO income (product, provider, date,
             count, price, nds) VALUES ('%s', '%s', '%s', %d, %f, %d) 
             """ % (product, provider, data, count, price, nds))
def get_income(date):
    table = []
    try:
        c = db.connect(database=database_name)
        cu = c.cursor()
        cu.execute("SELECT * FROM income WHERE date = '%s';" % date)
        for i in cu.fetchall():
            table.append({
            'id': i[0],
            'product': i[1],
            'provider': i[2],
            'date': i[3],
            'nds': i[4],
            'count': i[5],
            'price': i[6]})            
    except db.DatabaseError, x:
        print "Error: ", x
    return table

def upd_income():
    pass
def del_income(id):
    return exec_sql("DELETE FROM income WHERE id=%d;" % id)
    
def print_table(table_name):
    try:
        c = db.connect(database=database_name)
        cu = c.cursor()
        cu.execute("SELECT * FROM %s;" % table_name)
        print table_name
        for f in cu.description:
            print f[0],
        print 
        for r in cu.fetchall():
            l = ""
            for j in r:
                l += str(j) + " "
            print l
        print 
    except db.DatabaseError, x:
        print "Error: ", x


        
if __name__ == '__main__':
    p = income()
    print p.get_incomes()
    for r in  p.get_incomes():
        print r
     #   print r['count']
    
    #print_table('income')
    